package com.kaikeba.dao.imp;

import com.kaikeba.bean.Express;
import com.kaikeba.dao.BaseExpressDao;
import com.kaikeba.exception.DuplicateCodeException;
import com.kaikeba.util.DruidUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExpressDaoMysql implements BaseExpressDao {

    private static final String SQL_CONSOLE = "SELECT COUNT(ID) SIZE1, COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) OR NULL) DAY1, COUNT(STATUS=0 OR NULL) SIZE2, COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) AND STATUS=0 OR NULL) DAY2 FROM EXPRESS;";
    private static final String SQL_FIND_ALL = "SELECT * FROM EXPRESS";
    private static final String SQL_FIND_LIMIT = "SELECT * FROM EXPRESS LIMIT ?,?";
    private static final String SQL_FIND_BY_ID = "SELECT * FROM EXPRESS WHERE ID=?";
    private static final String SQL_FIND_BY_NUMBER = "SELECT * FROM EXPRESS WHERE NUMBER=?";
    private static final String SQL_FIND_BY_CODE = "SELECT * FROM EXPRESS WHERE CODE=?";
    private static final String SQL_FIND_BY_USERPHONE = "SELECT * FROM EXPRESS WHERE USERPHONE=?";
    private static final String SQL_FIND_BY_SYSPHONE = "SELECT * FROM EXPRESS WHERE SYSPHONE=?";
    private static final String SQL_INSERT = "INSERT INTO EXPRESS (ID, NUMBER, USERNAME, USERPHONE, COMPANY, CODE, INTIME, STATUS, SYSPHONE) VALUES (?, ?, ?, ?, ?, ?, NOW(), 0, ?)";
    private static final String SQL_UPDATE = "UPDATE EXPRESS SET NUMBER=?, USERNAME=?, COMPANY=?, STATUS=? WHERE ID=?";
    private static final String SQL_DELETE = "DELETE FROM EXPRESS WHERE ID=?";
    private static final String SQL_UPDATE_STATUE = "UPDATE EXPRESS SET STATUS=1, OUTTIME=NOW(), CODE=NULL WHERE CODE=?";

    /**
     *
     *
     * 用于获取控制台所需的快递数据
     *
     * @return [{size:总数,day:新增}, {size:未取件总数,day:未取件新增}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        List<Map<String, Integer>> list = new ArrayList<>();
        try {
            state = conn.prepareStatement(SQL_CONSOLE);
            result = state.executeQuery();
            if(result.next()){
                Map<String, Integer> data1 = new HashMap<>();
                data1.put("size", result.getInt("SIZE1"));
                data1.put("day", result.getInt("DAY1"));
                list.add(data1);
                Map<String, Integer> data2 = new HashMap<>();
                data2.put("size", result.getInt("SIZE2"));
                data2.put("day", result.getInt("DAY2"));
                list.add(data2);
                return list;
            }else{
                return null;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return null;
    }

    /**
     * 获取快件列表（分页）
     *
     * @param limit      是否分页，0表示关闭分页
     * @param offset    sql语句开始的索引
     * @param pageNumber 获取快递的数量
     * @return 快件列表
     */
    @Override
    public List<Express> findAll(int limit, int offset, int pageNumber) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        List<Express> list = new ArrayList<>();
        try {
            if(limit == 1){
                state = conn.prepareStatement(SQL_FIND_LIMIT);
                state.setInt(1, offset);
                state.setInt(2, pageNumber);
            }else{
                state = conn.prepareStatement(SQL_FIND_ALL);
            }
            result = state.executeQuery();
            while(result.next()){
                Express e = new Express();
                e.setId(result.getInt("id"));
                e.setNumber(result.getString("number"));
                e.setUsername(result.getString("username"));
                e.setUserPhone(result.getString("userPhone"));
                e.setCompany(result.getString("company"));
                e.setCode(result.getString("code"));
                e.setInTime(result.getTimestamp("intime"));
                e.setOutTime(result.getTimestamp("outtime"));
                e.setStatus(result.getInt("status"));
                e.setSysPhone(result.getString("sysPhone"));
                list.add(e);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return null;
    }

    @Override
    public Express findById(int id){
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;

        try {
            state = conn.prepareStatement(SQL_FIND_BY_ID);
            state.setString(1, Integer.toString(id));
            result = state.executeQuery();
            if(result.next()){
                Express e = new Express();
                e.setId(result.getInt("id"));
                e.setNumber(result.getString("number"));
                e.setUsername(result.getString("username"));
                e.setUserPhone(result.getString("userPhone"));
                e.setCompany(result.getString("company"));
                e.setCode(result.getString("code"));
                e.setInTime(result.getTimestamp("intime"));
                e.setOutTime(result.getTimestamp("outtime"));
                e.setStatus(result.getInt("status"));
                e.setSysPhone(result.getString("sysPhone"));
                return e;
            }else{
                return null;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return null;
    }

    /**
     * 根据单号查询快件
     *
     * @param number 单号
     * @return 快件
     */
    @Override
    public Express findByNumber(String number) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;

        try {
            state = conn.prepareStatement(SQL_FIND_BY_NUMBER);
            state.setString(1, number);
            result = state.executeQuery();
            if(result.next()){
                Express e = new Express();
                e.setId(result.getInt("id"));
                e.setNumber(result.getString("number"));
                e.setUsername(result.getString("username"));
                e.setUserPhone(result.getString("userPhone"));
                e.setCompany(result.getString("company"));
                e.setCode(result.getString("code"));
                e.setInTime(result.getTimestamp("intime"));
                e.setOutTime(result.getTimestamp("outtime"));
                e.setStatus(result.getInt("status"));
                e.setSysPhone(result.getString("sysPhone"));
                return e;
            }else{
                return null;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return null;
    }

    /**
     * 根据取件码查询快递信息
     *
     * @param code 取件码
     * @return 快件
     */
    @Override
    public Express findByCode(String code) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;

        try {
            state = conn.prepareStatement(SQL_FIND_BY_CODE);
            state.setString(1, code);
            result = state.executeQuery();
            if(result.next()){
                Express e = new Express();
                e.setId(result.getInt("id"));
                e.setNumber(result.getString("number"));
                e.setUsername(result.getString("username"));
                e.setUserPhone(result.getString("userPhone"));
                e.setCompany(result.getString("company"));
                e.setCode(result.getString("code"));
                e.setInTime(result.getTimestamp("intime"));
                e.setOutTime(result.getTimestamp("outtime"));
                e.setStatus(result.getInt("status"));
                e.setSysPhone(result.getString("sysPhone"));
                return e;
            }else{
                return null;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return null;
    }

    /**
     * 根据用户的手机号，查询快递信息
     *
     * @param phoneNumber 手机号
     * @return 快件列表
     */
    @Override
    public List<Express> findByUserPhone(String phoneNumber) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        List<Express> list = new ArrayList<>();
        try {
            state = conn.prepareStatement(SQL_FIND_BY_USERPHONE);
            state.setString(1, phoneNumber);
            result = state.executeQuery();
            while(result.next()){
                Express e = new Express();
                e.setId(result.getInt("id"));
                e.setNumber(result.getString("number"));
                e.setUsername(result.getString("username"));
                e.setUserPhone(result.getString("userPhone"));
                e.setCompany(result.getString("company"));
                e.setCode(result.getString("code"));
                e.setInTime(result.getTimestamp("intime"));
                e.setOutTime(result.getTimestamp("outtime"));
                e.setStatus(result.getInt("status"));
                e.setSysPhone(result.getString("sysPhone"));
                list.add(e);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, null);
        }
        return null;
    }

    /**
     * 根据录入人手机号查询快递
     *
     * @param sysPhone 录入人手机号
     * @return 快件列表
     */
    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        List<Express> list = new ArrayList<>();
        try {
            state = conn.prepareStatement(SQL_FIND_BY_SYSPHONE);
            state.setString(1, sysPhone);
            result = state.executeQuery();
            while(result.next()){
                Express e = new Express();
                e.setId(result.getInt("id"));
                e.setNumber(result.getString("number"));
                e.setUsername(result.getString("username"));
                e.setUserPhone(result.getString("userPhone"));
                e.setCompany(result.getString("company"));
                e.setCode(result.getString("code"));
                e.setInTime(result.getTimestamp("intime"));
                e.setOutTime(result.getTimestamp("outtime"));
                e.setStatus(result.getInt("status"));
                e.setSysPhone(result.getString("sysPhone"));
                list.add(e);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, null);
        }
        return null;
    }

    /**
     * 快件录入
     *
     * @param express 快件
     * @return 是否录入成功
     */
    @Override
    public boolean insert(Express express) throws DuplicateCodeException{
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            state.setInt(1, express.getId());
            state.setString(2, express.getNumber());
            state.setString(3, express.getUsername());
            state.setString(4, express.getUserPhone());
            state.setString(5, express.getCompany());
            state.setString(6, express.getCode());
            state.setString(7, express.getSysPhone());
            int result = state.executeUpdate();
            if(result >= 1){
                return true;
            }else{
                return false;
            }
        } catch (SQLException throwables) {
            if(throwables.getMessage().endsWith("for key 'code'")){
                throw new DuplicateCodeException(throwables.getMessage());
            }else{
                throwables.printStackTrace();
            }
        } finally {
            DruidUtil.close(conn, state, null);
        }
        return false;
    }

    /**
     * 根据快递id，修改快递信息
     *
     * @param id      要修改的快递id
     * @param express 新的快递信息
     * @return 是否修改成功
     */
    @Override
    public boolean update(int id, Express express) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            state.setString(1, express.getNumber());
            state.setString(2, express.getUsername());
            state.setString(3, express.getCompany());
            state.setInt(4, express.getStatus());
            state.setInt(5, express.getId());
            int result = state.executeUpdate();

            if(result >= 1){
                return true;
            }else{
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, null);
        }
        return false;
    }

    /**
     * 删除快件
     *
     * @param id 要删除的快件id
     * @return 是否删除成功
     */
    @Override
    public boolean delete(int id) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_DELETE);
            state.setInt(1, id);
            int result = state.executeUpdate();
            if(result >= 1){
                return true;
            }else{
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, null);
        }
        return false;
    }

    /**
     * 确认取件
     *
     * @param code 确认取件的快递单号
     * @return 是否更改成功
     */
    @Override
    public boolean updateStatus(String code) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE_STATUE);
            state.setString(1, code);
            int result = state.executeUpdate();
            if(result >= 1){
                return true;
            }else{
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, null);
        }
        return false;
    }
}
